<?php
namespace Dealer\Model;
/**
 * 财务
 */
class FinanceModel extends BaseModel{

    protected $tableName = 'order_cost_detail';
    /**
     * 财务结算
     */
    public  function  financeList($where){
        $count = M('order_cost_detail as cd')
            ->join("LEFT JOIN __PUBLIC_LINE__ as l on cd.line_id=l.line_id")
            ->join("LEFT JOIN __OPERATOR__ as o on o.operator_id=cd.operator_id")
            ->where($where)
            ->count();
        // 分页
        $Page  = new \Think\Page($count,25);// 实例化分页类 传入总记录数和每页显示的记录数(25)
        $show  = $Page->show();// 分页显示输出
        // 查询
        $list  =M('order_cost_detail as cd')
            ->join("LEFT JOIN __PUBLIC_LINE__ as l on cd.line_id=l.line_id")
            ->join("LEFT JOIN __OPERATOR__ as o on o.operator_id=cd.operator_id")
            ->join("LEFT JOIN __PUBLIC_GROUP__ as g on g.group_id=cd.group_id")
            ->join("LEFT JOIN __OPERATOR__ as op on op.operator_id=cd.closing_operator_id")
            ->where($where)
            ->field('cd.addition_money,cd.closing_time,cd.group_id,cd.id,cd.closing_status,cd.actually_money,cd.cost_total_money,l.line_name,l.line_sn,g.group_num,g.group_time,g.out_time,g.received_num,o.operator_name,g.group_status,op.operator_name as handle_user')
            ->limit($Page->firstRow.','.$Page->listRows)
            ->order('cd.group_id desc')
            ->select();

        $totalCostMoney=M('order_cost_detail as cd')
            ->join("LEFT JOIN __PUBLIC_LINE__ as l on cd.line_id=l.line_id")
            ->join("LEFT JOIN __OPERATOR__ as o on o.operator_id=cd.operator_id")
            ->where($where)
            ->sum('cd.actually_money');

        return ['list'=>$list,'show'=>$show,'totalCostMoney'=>$totalCostMoney];
    }


    /**
     * 发起结算/确认结算
     * @param $id 结算ID
     * @param $closeStatus 结算状态
     * @return array
     */
    public  function  groupWindup($id,$closeStatus){

        $info=M('order_cost_detail')->where(['id'=>$id])->find();

        if($closeStatus==1){
            if($info['closing_status']!=-1){
                return ['msg'=>'请勿重复操作!','status'=>-1];
            }
            $res= M('order_cost_detail')->where(['id'=>$id])->setField(['closing_status'=>$closeStatus]);

            //更新团结算状态为结算中
            $groupSave['closing_status']=1;
            M('public_group')->where(array('group_id'=>$info['group_id']))->save($groupSave);

        }else if($closeStatus==3){
            if($info['closing_status']!=2){
                return ['msg'=>'请勿重复操作!','status'=>-1];
            }
            $res= M('order_cost_detail')->where(['id'=>$id])->setField(['closing_status'=>$closeStatus]);
        }
        if($res){
            return ['msg'=>'操作成功!','status'=>1];
        }
        return ['msg'=>'操作失败!','status'=>-1];

    }

    /**
     * 返回结算状态
     * @param $status 状态
     * @return string 状态名称
     */
    private function closingStatusName($status){
        //-1待结算、1结算中、2已结算,3 确认完成结算
        $statusName='';
        switch ($status){
            case -1: $statusName='待结算';break;
            case 1: $statusName='结算中';break;
            case 2: $statusName='已结算';break;
            case 3: $statusName='确认完成结算';break;
        }
        return $statusName;
    }

    private  function groupStatusName($status){
        //状态(2已出团、1已成团、不成团-1、已回团3)默认0
        $statusName='';
        switch ($status){
            case 0: $statusName='待处理';break;
            case -1: $statusName='不成团';break;
            case 1: $statusName='已成团';break;
            case 2: $statusName='已出团';break;
            case 3: $statusName='已回团';break;
        }
        return $statusName;
    }


    /**
     * 财务结算导出excel
     */
    public  function  exportExcelFinanceList($where){

        $list=M('order_cost_detail as cd')
            ->join("LEFT JOIN __PUBLIC_LINE__ as l on cd.line_id=l.line_id")
            ->join("LEFT JOIN __OPERATOR__ as o on o.operator_id=cd.operator_id")
            ->join("LEFT JOIN __PUBLIC_GROUP__ as g on g.group_id=cd.group_id")
            ->join("LEFT JOIN __OPERATOR__ as op on op.operator_id=cd.closing_operator_id")
            ->where($where)
            ->field('cd.addition_money,cd.closing_time,cd.group_id,cd.id,cd.closing_status,cd.actually_money,cd.cost_total_money,l.line_name,l.line_sn,g.group_num,g.group_time,g.out_time,g.received_num,o.operator_name,g.group_status,op.operator_name as handle_user')
            ->order('g.group_id desc')
            ->where($where)
            ->select();
        foreach ($list as $k=>$v){
            $list[$k]['closing_status']=self::closingStatusName($v['closing_status']);
            $list[$k]['group_status']=self::groupStatusName($v['group_status']);
            $list[$k]['group_time']=date('Y-m-d',$v['group_time']);
            $list[$k]['out_time']=date('Y-m-d',$v['out_time']);
            $list[$k]['closing_time']=$v['closing_time']?date('Y-m-d',$v['closing_time']):"";
        }

        $expCellName  = array(
            array('line_name','线路名称'),
            array('line_sn','线路编号'),
            array('group_num','团号'),
            array('group_time','成团时间'),
            array('out_time','出团时间'),
            array('received_num','已收客'),
            array('addition_money','其它费用'),
            array('actually_money','结算总金额'),
           /* array('cost_total_money','结算总金额'),*/
            array('operator_name','合作运营商'),
            array('group_status','成团状态'),
            array('closing_status','结算状态'),
            array('closing_time','结算时间'),
            array('handle_user','结算人'),
        );
        $fileName='';
        if($where['cd.closing_status']==-1){
            $fileName="待结算列表";
        }else if($where['cd.closing_status']==1){
            $fileName="待结中列表";
        }else if($where['cd.closing_status']==2){
            $fileName="已结算列表";
        }

        parent::exportExcel($fileName,$expCellName,$list);

    }


    /**
     * 运营商结算明细
     */
    public function  operationList($where){
        $count      = M('line_orders as o')
            ->join("LEFT JOIN __PUBLIC_GROUP__ as g on o.group_id=g.group_id")
            ->join("__PUBLIC_LINE__ as pl on pl.line_id=g.line_id")
            ->join('__OPERATOR__ as op on op.operator_id=pl.operator_id')
            ->join("__SUPPLIER_LINE__ as sl on o.origin_line_id=sl.line_id and sl.supplier_id={$where['sl.supplier_id']}")
            ->where($where)
            ->count();// 查询满足要求的总记录数

        $Page       = new \Think\Page($count,25);// 实例化分页类 传入总记录数和每页显示的记录数(25)
        $show       = $Page->show();// 分页显示输出

        // 进行分页数据查询 注意limit方法的参数要使用Page类的属性
        $field="g.*,sl.line_name,sl.line_sn,op.operator_name,o.*,op1.admin2_name";
        $list=M('line_orders as o')
            ->join("LEFT JOIN __PUBLIC_GROUP__ as g on o.group_id=g.group_id")
            ->join("__PUBLIC_LINE__ as pl on pl.line_id=g.line_id")
            ->join('__OPERATOR__ as op on op.operator_id=pl.operator_id')
            ->join('__OPERATOR__ as op on op1.operator_id=g.admin2_id')
            ->join("__SUPPLIER_LINE__ as sl on o.origin_line_id=sl.line_id and sl.supplier_id={$where['sl.supplier_id']}")
            ->field($field)
            ->order('g.order_id desc')
            ->where($where)
            ->limit($Page->firstRow.','.$Page->listRows)
            ->select();


        //结算金额->跟供应商的结算价格
        foreach ($list as $k=>$v){
            $list[$k]['close_money']=$v['adult_num']*$v['adult_cost_money']+$v['child_num']*$v['child_cost_money']+$v['oldMan_num']*$v['oldman_cost_money'];
        }

        $total=M('line_orders as o')
            ->join("LEFT JOIN __PUBLIC_GROUP__ as g on o.group_id=g.group_id")
            ->join("__PUBLIC_LINE__ as pl on pl.line_id=g.line_id")
            ->join('__OPERATOR__ as op on op.operator_id=pl.operator_id')
            ->join("__SUPPLIER_LINE__ as sl on o.origin_line_id=sl.line_id and sl.supplier_id={$where['sl.supplier_id']}")
            ->where($where)
            ->SUM("adult_cost_money *adult_num + child_cost_money*child_num + oldman_cost_money*oldMan_num");



        return ['list'=>$list,'show'=>$show,'total'=>$total];
    }


    /**
     * 运营商结算导出表格
     */
    public function  exportExcelOperationList($where){

        // 进行分页数据查询 注意limit方法的参数要使用Page类的属性
        $field="g.*,sl.line_name,sl.line_sn,op.operator_name,o.*,op1.admin2_name";
        $list=M('line_orders as o')
            ->join("LEFT JOIN __PUBLIC_GROUP__ as g on o.group_id=g.group_id")
            ->join("__PUBLIC_LINE__ as pl on pl.line_id=g.line_id")
            ->join('__OPERATOR__ as op on op.operator_id=pl.operator_id')
            ->join('__OPERATOR__ as op on op1.operator_id=g.admin2_id')
            ->join("__SUPPLIER_LINE__ as sl on o.origin_line_id=sl.line_id and sl.supplier_id={$where['sl.supplier_id']}")
            ->field($field)
            ->order('g.order_id desc')
            ->where($where)
            ->select();


        foreach ($list as $k=>$v){
            $list[$k]['create_time']=date('Y-m-d',$v['create_time']);
            $list[$k]['close_time']=date('Y-m-d',$v['close_time']);
            $list[$k]['close_money']=$v['adult_num']*$v['adult_cost_money']+$v['child_num']*$v['child_cost_money']+$v['oldMan_num']*$v['oldman_cost_money'];
        }

        $expCellName  = array(
            array('order_id','ID'),
            array('line_name','线路名称'),
            array('group_type','团队类型'),
            array('line_sn','产品编号'),
            array('group_num','团号'),
            array('operator_name','运营商'),
            array('create_time','下单时间'),
            array('adult_num','成人人数'),
            array('child_num','儿童人数'),
            //array('oldMan_num','老人人数'),
            array('adult_cost_money','成人结算金额'),
            array('child_cost_money','儿童结算金额'),
            //array('oldman_cost_money','老人结算金额'),
            array('else_money','其他费用'),
            array('close_money','总结算金额'),
            array('close_time','结算时间'),
            array('admin2_name','操作人')
        );
        $fileName=$list[0]['group_num'].$list[0]['out_time'].'运营商结算表';
        parent::exportExcel($fileName,$expCellName,$list);
    }


    /**
     * 营业收入报表
     */
    public  function  reportList($where){

        $count      = M('order_cost_detail as cd')
            ->join("__PUBLIC_GROUP__ as g on cd.group_id=g.group_id")
            ->join("__PUBLIC_LINE__ as l on l.line_id=cd.line_id")
            ->where($where)
            ->count();// 查询满足要求的总记录数
        $Page       = new \Think\Page($count,25);// 实例化分页类 传入总记录数和每页显示的记录数(25)
        $show       = $Page->show();// 分页显示输出

        // 进行分页数据查询 注意limit方法的参数要使用Page类的属性
        $field="g.group_num,l.line_name,cd.closing_time,cd.supplier_unit_cost_total,cd.cost_total_money,cd.actually_money,g.received_num,g.group_time,g.out_time";
        $list=M('order_cost_detail as cd')
            ->join("__PUBLIC_GROUP__ as g on cd.group_id=g.group_id")
            ->join("__PUBLIC_LINE__ as l on l.line_id=cd.line_id")
            ->where($where)
            ->field($field)
            ->order('g.group_id desc')
            ->limit($Page->firstRow.','.$Page->listRows)
            ->select();

        $totalInfo=M('order_cost_detail as cd')
            ->join("__PUBLIC_GROUP__ as g on cd.group_id=g.group_id")
            ->join("__PUBLIC_LINE__ as l on l.line_id=cd.line_id")
            ->where($where)
            ->field($field)
            ->field("sum(cost_total_money)as cost_total_money,sum(actually_money) as actually_money,sum(supplier_unit_cost_total) as supplier_unit_cost_total")
            ->find();

        return ['list'=>$list,'show'=>$show,'total'=>$totalInfo['actually_money'],'gross'=>$totalInfo['actually_money']-$totalInfo['supplier_unit_cost_total']];
    }


    /**
     * 营业收入报表导出excel
     */
    public  function  exportExcelReport($where){
        $field="g.group_num,l.line_name,cd.closing_time,cd.supplier_unit_cost_total,cd.cost_total_money,cd.actually_money,g.received_num,g.group_time,g.out_time";
        $list=M('order_cost_detail as cd')
            ->join("__PUBLIC_GROUP__ as g on cd.group_id=g.group_id")
            ->join("__PUBLIC_LINE__ as l on l.line_id=cd.line_id")
            ->where($where)
            ->order('g.group_id desc')
            ->field($field)
            ->select();

        foreach ($list as $k=>$v){
            $list[$k]['line_name']=$v['group_num'].",".$v['line_name'];
            $list[$k]['closing_time']=$v['closing_time']?date('Y-m-d',$v['closing_time']):'';
            $list[$k]['group_time']=$v['group_time']?date('Y-m-d',$v['group_time']):'';
            $list[$k]['out_time']=$v['out_time']?date('Y-m-d',$v['out_time']):'';
            $list[$k]['gross']=$v['actually_money']-$v['supplier_unit_cost_total'];
            $list[$k]['gross_profit']=sprintf("%.2f",($list[$k]['gross']/$v['actually_money']*100))."%";

        }

        $expCellName  = array(
            array('line_name','订单信息'),
            array('closing_time','结算时间'),
            array('group_time','成团时间'),
            array('received_num','总人数'),
            array('actually_money','结算总额'),
            array('supplier_unit_cost_total','总成本'),
            array('gross','毛利'),
            array('gross_profit','毛利率'),

        );
        $fileName=$list[0]['group_num'].$list[0]['out_time'].'营业收入报表';
        parent::exportExcel($fileName,$expCellName,$list);

    }


    /**
     * 团号详情
     */
    public  function  groupOrderDetail($group_id){
        $groupInfo=M('public_group')->where(['group_id'=>$group_id])->find();
        //线路出发地
        $lineInfo=M('public_line')->where(['line_id'=>$groupInfo['line_id']])->find();
        $starting=M('areas')->where(['areaId'=>$lineInfo['origin_id']])->getField('areaName');
        $groupStatusName=self::groupStatusName($groupInfo['group_status']);

        $where['o.group_id']=$group_id;
        $where['o.order_status']=array('gt',0);

        $count=M('line_orders as o')
            ->where($where)
            ->count();

        $Page       = new \Think\Page($count,25);// 实例化分页类 传入总记录数和每页显示的记录数(25)
        $show       = $Page->show();// 分页显示输出

        $field="o.order_id,o.order_num,t.tourists_name,t.tourists_phone,o.adult_num,o.child_num,o.oldMan_num,o.order_status,o.total_num,op.operator_name";
        $list=M('line_orders as o')
            ->join("__PUBLIC_TOURISTS__ as t on t.tourists_id=o.contact_id")
            ->join("__OPERATOR__ as op on op.operator_id=o.verifier_id")
            ->field($field)
            ->where($where)
            ->limit($Page->firstRow.','.$Page->listRows)
            ->select();

        foreach ($list as $k=>$v){
            $list[$k]['starting']=$starting;
            $list[$k]['line_name']=$lineInfo['line_name'];
            $list[$k]['line_sn']=$lineInfo['line_sn'];
            $list[$k]['travel_days']=$lineInfo['travel_days'];
            $list[$k]['groupStatusName']=$groupStatusName;
        }
        return ['list'=>$list,'show'=>$show,'group'=>$groupInfo];
    }


    /**
     * 导出团号订单列表
     */
    public  function exportGroupOrderList($group_id){

        $groupInfo=M('public_group')->where(['group_id'=>$group_id])->find();
        //线路出发地
        $lineInfo=M('public_line')->where(['line_id'=>$groupInfo['line_id']])->find();
        $starting=M('areas')->where(['areaId'=>$lineInfo['origin_id']])->getField('areaName');
        $groupStatusName=self::groupStatusName($groupInfo['group_status']);

        $where['o.group_id']=$group_id;
        $where['o.order_status']=array('gt',0);

        $field="o.order_id,o.order_num,t.tourists_name,t.tourists_phone,o.adult_num,o.child_num,o.oldMan_num,o.order_status,o.total_num,op.operator_name";
        $list=M('line_orders as o')
            ->join("__PUBLIC_TOURISTS__ as t on t.tourists_id=o.contact_id")
            ->join("__OPERATOR__ as op on op.operator_id=o.verifier_id")
            ->field($field)
            ->where($where)
            ->select();

        foreach ($list as $k=>$v){
            $list[$k]['starting']=$starting;
            $list[$k]['line_name']=$lineInfo['line_name'];
            $list[$k]['line_sn']=$lineInfo['line_sn'];
            $list[$k]['travel_days']=$lineInfo['travel_days'];
            $list[$k]['groupStatusName']=$groupStatusName;
            $list[$k]['adult_num']="成{$v['adult_num']},儿{$v['child_num']}";
        }

        $expCellName  = array(
            array('order_num','订单号'),
            array('tourists_name','游客姓名'),
            array('tourists_phone','联系方式'),
            array('line_name','线路名称'),
            array('line_sn','线路编号'),
            array('starting','出发地'),
            array('travel_days','行程天数'),
            array('total_num','总人数'),
            array('adult_num','人数明细'),
            array('groupStatusName','状态'),
            array('operator_name','审核人'),
        );
        $fileName=$groupInfo['group_num'].'团号订单详情';
        parent::exportExcel($fileName,$expCellName,$list);
    }


}

